package cn.ctbu.xiaopeng.studentscoremanage.dao;

import cn.ctbu.xiaopeng.studentscoremanage.pojo.dto.UserQueryDTO;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.entity.Student;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.entity.User;
import cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.UserPageVO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @author xiaopeng
 * @version 1.0
 */
public interface UserRepository extends JpaRepository<User, Integer> {

    @Query("SELECT u FROM tb_user u WHERE u.userName = :userName")
    User findByUserName(@Param("userName") String userName);


    /**
     * 根据用户名查询用户信息，且用户被锁定（isLocked = 1）
     *
     * @param userName 用户名
     * @return 用户信息
     */
    @Query("SELECT u FROM tb_user u WHERE u.userName = :userName AND u.isLocked = 1")
    User findLockedUserByUserName(@Param("userName") String userName);


    /**
     * 根据id批量删除
     *
     * @param ids
     */
    @Modifying
    @Query("DELETE FROM tb_user u WHERE u.id IN :ids")
    void deleteByIds(List<Integer> ids);


    /**
     * 根据条件分页查询用户信息
     *
     * @param userQueryDTO
     * @param pageable
     * @return
     */
    @Query("SELECT new cn.ctbu.xiaopeng.studentscoremanage.pojo.vo.UserPageVO(" +
            "u.id, u.userName, u.roleId, " +
            "CASE " +
            "  WHEN u.userType = 1 THEN '管理员' " +
            "  WHEN u.userType = 2 THEN t.name " +
            "  WHEN u.userType = 3 THEN s.name " +
            "  ELSE '未知' " +
            "END, " +  // 不使用 AS 进行别名映射，直接返回查询结果
            "u.password, u.userType, u.isLocked, u.registerTime) " +
            "FROM tb_user u " +
            "LEFT JOIN tb_teacher t ON u.roleId = t.tno AND u.userType = 2 " +
            "LEFT JOIN tb_student s ON u.roleId = s.sno AND u.userType = 3 " +
            "WHERE (:#{#userQueryDTO.userName} IS NULL OR u.userName LIKE %:#{#userQueryDTO.userName}%) " +
            "AND (:#{#userQueryDTO.roleId} IS NULL OR u.roleId = :#{#userQueryDTO.roleId}) " +
            "AND (:#{#userQueryDTO.userType} IS NULL OR u.userType = :#{#userQueryDTO.userType}) " +
            "AND (:#{#userQueryDTO.isLocked} IS NULL OR u.isLocked = :#{#userQueryDTO.isLocked}) " +
            "AND (:#{#userQueryDTO.roleName} IS NULL OR " +
            "  (u.userType = 1 AND '管理员' LIKE %:#{#userQueryDTO.roleName}%) OR " +
            "  (u.userType = 2 AND t.name LIKE %:#{#userQueryDTO.roleName}%) OR " +
            "  (u.userType = 3 AND s.name LIKE %:#{#userQueryDTO.roleName}%))")
    Page<UserPageVO> getUserPageByConditions(@Param("userQueryDTO") UserQueryDTO userQueryDTO, Pageable pageable);


    @Transactional
    @Modifying
    @Query("UPDATE tb_user u SET u.isLocked = :isLocked WHERE u.id = :userId")
    void changeUserIsLocked(@Param("userId") String userId, @Param("isLocked") Integer isLocked);


}
